CREATE DATABASE Etown
GO

USE Etown
GO

CREATE TABLE GroupAccounts
(
	GroupId INT IDENTITY(1,1) PRIMARY KEY,
	GroupName NVARCHAR(30) NOT NULL
)
GO

CREATE TABLE Account
(
	AccountId INT IDENTITY(1,1) PRIMARY KEY,
	GroupId INT FOREIGN KEY REFERENCES GroupAccounts(GroupId) ON DELETE SET NULL ON UPDATE SET NULL,	
	LoginName NVARCHAR(50)NOT NULL ,
	[Password] NVARCHAR(50) NOT NULL,
	Name NVARCHAR(50) NOT NULL,
	CompanyName NVARCHAR(50) NOT NULL,	
	Birthday DATETIME NOT NULL,
	Sex NVARCHAR(6) NOT NULL,
	Cmnd INT NOT NULL,
	[Address] NVARCHAR(50) NOT NULL,
	Phone NVARCHAR(12) NOT NULL,
	Email NVARCHAR(100) NOT NULL,
	IssueDay DATETIME NOT NULL DEFAULT (getdate()),
    CHECK(phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
    OR phone LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
	
)
GO

CREATE TABLE Town
(
	TownId INT IDENTITY(1,1) PRIMARY KEY ,	
	TownName NVARCHAR(50) NOT NULL,
	NumberFloor INT NOT NULL,
	[Status] INT NOT NULL	
)
GO

CREATE TABLE [Floor]
(
	FloorId INT IDENTITY(1,1) PRIMARY KEY,
	TownId INT FOREIGN KEY REFERENCES Town(TownId) NOT NULL,
	FloorName NVARCHAR(50) NOT NULL,	
	[Status] INT NOT NULL
)
GO

CREATE TABLE BillPaymentOfService
(
	BillPaymentID INT IDENTITY(1,1) PRIMARY KEY,
	[Security] MONEY ,
	WashingRooms MONEY ,
	CleanBuildingGlasses MONEY ,
	MaintenanceElevator MONEY,
	AmountOfParking INT
)
GO

CREATE TABLE Bill
(
	BillId INT IDENTITY(1,1) PRIMARY KEY,    
	AccountId INT FOREIGN KEY REFERENCES Account(AccountId),
	BillPaymentID INT FOREIGN KEY REFERENCES BillPaymentOfService(BillPaymentID) NOT NULL,                                                                
	[Power] MONEY ,
	Water MONEY ,
	DateOfPayment DATETIME NOT NULL DEFAULT (getdate()),
	[Status] INT NOT NULL
)
GO

CREATE TABLE Unit
(
	UnitId INT IDENTITY(1,1)PRIMARY KEY,	
	FloorId INT FOREIGN KEY REFERENCES [Floor](FloorId) NOT NULL,
	BillId INT FOREIGN KEY REFERENCES Bill(BillId),
	AccountId INT FOREIGN KEY REFERENCES Account(AccountId ),
	UnitName NVARCHAR(50) NOT NULL,
	UnitPrice MONEY NOT NULL,
	[Status] INT NOT NULL,
	Decorate INT NOT null,
)
GO





